using System;
using System.Data;
using PCSComMaterials.Inventory.DS;
using PCSComUtils.Common;
using PCSComUtils.MasterSetup.DS;
using PCSComUtils.PCSExc;
using PCSComUtils.DataContext;
using System.Linq;
using System.Transactions;
using PCSComUtils.DataAccess;
using log4net;

namespace PCSComMaterials.Inventory.BO
{
	/// <summary>
	/// Summary description for IVLocToLocTransferBO.
	/// </summary>
	public class IVMiscellaneousIssueBO
	{
	    private ILog _logger = LogManager.GetLogger(typeof (IVMiscellaneousIssueBO));
		/// <summary>
		/// Get all LocToLoc detail by MasterID
		/// </summary>
		/// <param name="pintcMasterID"></param>
		/// <returns></returns>
		public DataSet GetMiscellaneousByMaster(int pintcMasterID)
		{
			return new IV_MiscellaneousIssueDetailDS().GetMiscellaneousDetailByMaster(pintcMasterID);
		}
		public DataSet GetDetailWithRate(int pintcMasterID)
		{
			return new IV_MiscellaneousIssueDetailDS().GetDetailWithRate(pintcMasterID);
		}
		
		/// <summary>
		/// Get LocToLoc Master infor by ID
		/// </summary>
		/// <param name="pintMasterID"></param>
		/// <returns></returns>
		public DataRow GetMiscellaneousMasterInfor(int pintMasterID)
		{
			return new IV_MiscellaneousIssueMasterDS().GetMiscellaneousMasterInfor(pintMasterID);
		}

		/// <summary>
		/// Delete MiscellaneousIssue infor by ID
		/// </summary>
		/// <param name="issueMasterId">Misc Issue Master ID</param>
		public void DeleteMiscellaneousIssue(int issueMasterId)
		{
			//1. Variable
			const int enmInspStatus = 24;
			const int miscIssueTranType = 24;
			const int deleteTranType = 27;

            using (var trans = new TransactionScope())
            {
                try
                {
                    using (var db = new PCSDataContext(Utils.Instance.ConnectionString))
                    {
                        var issueMaster = db.IV_MiscellaneousIssueMasters.FirstOrDefault(m => m.MiscellaneousIssueMasterID == issueMasterId);

                        foreach (IV_MiscellaneousIssueDetail issueDetail in issueMaster.IV_MiscellaneousIssueDetails)
                        {
                            #region preparation

                            var product = issueDetail.ITM_Product;
                            var decQuantityReturn = issueDetail.Quantity;
                            decimal decRate = 1;
                            if (!product.Revision.Equals("CTTC", StringComparison.OrdinalIgnoreCase))
                            {
                                decRate = product.Rate.GetValueOrDefault(0);
                            }

                            // Xuat vat tu xuong xi nghiep san xuat
                            if (issueMaster.RequiredMaterialMasterID > 0)
                            {
                                // we need to subtract actual received quantity from required material transaction
                                var requiredDetail = issueMaster.PRO_RequiredMaterialMaster.PRO_RequiredMaterialDetails.FirstOrDefault(
                                        rd => rd.ProductID == product.ProductID);
                                requiredDetail.ActualReceived = requiredDetail.ActualReceived.GetValueOrDefault(0) - decQuantityReturn;
                                decQuantityReturn = decQuantityReturn*decRate;
                            }

                            #endregion

                            #region add quantity back to source

                            var sourceBin = db.IV_BinCaches.FirstOrDefault(b => b.BinID == issueMaster.SourceBinID && b.LocationID == issueMaster.SourceLocationID
                                     && b.MasterLocationID == issueMaster.SourceMasLocationID &&
                                     b.ProductID == product.ProductID);
                            if (sourceBin != null)
                            {
                                sourceBin.OHQuantity = sourceBin.OHQuantity.GetValueOrDefault(0) + decQuantityReturn;
                            }
                            else
                            {
                                sourceBin = new IV_BinCache
                                {
                                    CCNID = issueMaster.CCNID,
                                    MasterLocationID = issueMaster.SourceMasLocationID,
                                    LocationID = issueMaster.SourceLocationID,
                                    BinID = issueMaster.SourceBinID.GetValueOrDefault(0),
                                    ProductID = product.ProductID,
                                    OHQuantity = decQuantityReturn
                                };
                                db.IV_BinCaches.InsertOnSubmit(sourceBin);
                            }

                            var sourceLoc = db.IV_LocationCaches.FirstOrDefault(b => b.LocationID == issueMaster.SourceLocationID
                                     && b.MasterLocationID == issueMaster.SourceMasLocationID &&
                                     b.ProductID == product.ProductID);
                            if (sourceLoc != null)
                            {
                                sourceLoc.OHQuantity = sourceLoc.OHQuantity.GetValueOrDefault(0) + decQuantityReturn;
                            }
                            else
                            {
                                sourceLoc = new IV_LocationCache
                                {
                                    CCNID = issueMaster.CCNID,
                                    MasterLocationID = issueMaster.SourceMasLocationID,
                                    LocationID = issueMaster.SourceLocationID,
                                    ProductID = product.ProductID,
                                    OHQuantity = decQuantityReturn
                                };
                                db.IV_LocationCaches.InsertOnSubmit(sourceLoc);
                            }

                            var sourceMasLoc = db.IV_MasLocCaches.FirstOrDefault(b => b.MasterLocationID == issueMaster.SourceMasLocationID && b.ProductID == product.ProductID);
                            if (sourceMasLoc != null)
                            {
                                sourceMasLoc.OHQuantity = sourceMasLoc.OHQuantity.GetValueOrDefault(0) + decQuantityReturn;
                            }
                            else
                            {
                                sourceMasLoc = new IV_MasLocCache
                                {
                                    CCNID = issueMaster.CCNID,
                                    MasterLocationID = issueMaster.SourceMasLocationID,
                                    ProductID = product.ProductID,
                                    OHQuantity = decQuantityReturn
                                };
                                db.IV_MasLocCaches.InsertOnSubmit(sourceMasLoc);
                            }

                            #endregion

                            if (issueMaster.DesBinID > 0 || issueMaster.DesLocationID > 0)
                            {
                                #region substract quantity from destination

                                var destBin = db.IV_BinCaches.FirstOrDefault(b => b.BinID == issueMaster.DesBinID && b.LocationID == issueMaster.DesLocationID
                                         && b.MasterLocationID == issueMaster.DesMasLocationID &&
                                         b.ProductID == product.ProductID);
                                if (destBin != null)
                                {
                                    destBin.OHQuantity = destBin.OHQuantity.GetValueOrDefault(0) - decQuantityReturn;
                                }

                                var destLoc = db.IV_LocationCaches.FirstOrDefault(b => b.LocationID == issueMaster.SourceLocationID
                                         && b.MasterLocationID == issueMaster.SourceMasLocationID &&
                                         b.ProductID == product.ProductID);
                                if (destLoc != null)
                                {
                                    destLoc.OHQuantity = destLoc.OHQuantity.GetValueOrDefault(0) - decQuantityReturn;
                                }

                                var destMasLoc = db.IV_MasLocCaches.FirstOrDefault(b => b.MasterLocationID == issueMaster.SourceMasLocationID && b.ProductID == product.ProductID);
                                if (destMasLoc != null)
                                {
                                    destMasLoc.OHQuantity = destMasLoc.OHQuantity.GetValueOrDefault(0) - decQuantityReturn;
                                }

                                #endregion
                            }
                        }

                        var historyUpdated = db.MST_TransactionHistories.Where(t => t.RefMasterID == issueMasterId && t.TranTypeID == miscIssueTranType).ToList();
                        historyUpdated.ForEach(h =>
                        {
                            h.TranTypeID = deleteTranType;
                            h.InspStatus = enmInspStatus;
                        });

                        db.SubmitChanges();

                        #region update inventory data

                        // update quantity for source location and master location
                        // by compute the sum quantity from bin
                        var productList = issueMaster.IV_MiscellaneousIssueDetails.Select(d => d.ProductID);
                        foreach (int productId in productList)
                        {
                            #region Location cache

                            #region source location

                            var query = db.IV_BinCaches.Where(b => b.ProductID == productId && b.LocationID == issueMaster.SourceLocationID);
                            decimal onhandQuantity;
                            decimal commitQuantity;
                            if (query != null && query.Any())
                            {
                                onhandQuantity = query.Sum(b => b.OHQuantity.GetValueOrDefault(0));
                                commitQuantity = query.Sum(b => b.CommitQuantity.GetValueOrDefault(0));
                                var location = db.IV_LocationCaches.FirstOrDefault(l => l.ProductID == productId && l.LocationID == issueMaster.SourceLocationID);
                                if (location != null)
                                {
                                    location.OHQuantity = onhandQuantity;
                                    location.CommitQuantity = commitQuantity;
                                }
                                else
                                {
                                    location = new IV_LocationCache
                                                   {
                                                       CCNID = issueMaster.CCNID,
                                                       LocationID = issueMaster.SourceLocationID,
                                                       MasterLocationID = issueMaster.SourceMasLocationID,
                                                       ProductID = productId,
                                                       OHQuantity = onhandQuantity,
                                                       CommitQuantity = commitQuantity
                                                   };
                                    db.IV_LocationCaches.InsertOnSubmit(location);
                                }
                            }

                            #endregion

                            #region destionation location

                            if (issueMaster.DesLocationID.HasValue)
                            {
                                query = db.IV_BinCaches.Where(b => b.ProductID == productId && b.LocationID == issueMaster.DesLocationID);
                                if (query != null && query.Any())
                                {
                                    onhandQuantity = query.Sum(b => b.OHQuantity.GetValueOrDefault(0));
                                    commitQuantity = query.Sum(b => b.CommitQuantity.GetValueOrDefault(0));
                                    var location = db.IV_LocationCaches.FirstOrDefault(l => l.ProductID == productId && l.LocationID == issueMaster.DesLocationID);
                                    if (location != null)
                                    {
                                        location.OHQuantity = onhandQuantity;
                                        location.CommitQuantity = commitQuantity;
                                    }
                                    else
                                    {
                                        location = new IV_LocationCache
                                        {
                                            CCNID = issueMaster.CCNID,
                                            LocationID = (int)issueMaster.DesLocationID,
                                            MasterLocationID = issueMaster.SourceMasLocationID,
                                            ProductID = productId,
                                            OHQuantity = onhandQuantity,
                                            CommitQuantity = commitQuantity
                                        };
                                        db.IV_LocationCaches.InsertOnSubmit(location);
                                    }
                                }
                            }

                            #endregion


                            #endregion

                            #region master location cache

                            query = db.IV_BinCaches.Where(b => b.ProductID == productId && b.MasterLocationID == issueMaster.SourceMasLocationID);
                            if (query != null && query.Any())
                            {
                                onhandQuantity = query.Sum(b => b.OHQuantity.GetValueOrDefault(0));
                                commitQuantity = query.Sum(b => b.CommitQuantity.GetValueOrDefault(0));
                                var masLocCache = db.IV_MasLocCaches.FirstOrDefault(l => l.ProductID == productId && l.MasterLocationID == issueMaster.SourceMasLocationID);
                                if (masLocCache != null)
                                {
                                    masLocCache.OHQuantity = onhandQuantity;
                                    masLocCache.CommitQuantity = commitQuantity;
                                }
                                else
                                {
                                    masLocCache = new IV_MasLocCache
                                    {
                                        CCNID = issueMaster.CCNID,
                                        MasterLocationID = issueMaster.SourceMasLocationID,
                                        ProductID = productId,
                                        OHQuantity = onhandQuantity,
                                        CommitQuantity = commitQuantity
                                    };
                                    db.IV_MasLocCaches.InsertOnSubmit(masLocCache);
                                }
                            }

                            #endregion
                        }

                        #endregion

                        // delete detail first
                        db.IV_MiscellaneousIssueDetails.DeleteAllOnSubmit(issueMaster.IV_MiscellaneousIssueDetails);
                        // delete master
                        db.IV_MiscellaneousIssueMasters.DeleteOnSubmit(issueMaster);
                        db.SubmitChanges();
                    }
                }
                catch (Exception ex)
                {
                    _logger.Error("Failed to delete transaction", ex);
                    throw;
                }

                trans.Complete();
            }
		}

		public string GetCodeFromID(int pintBinID)
		{
			var dsBin = new MST_BINDS();
			return dsBin.GetCodeFromID(pintBinID);
		}

		public DataSet GetDataFromRequiredMaterial(int pintRequiredMasterID, int pintBinID)
		{
			var dsDetail = new IV_MiscellaneousIssueDetailDS();
			return dsDetail.GetDataForMiscIssue(pintRequiredMasterID, pintBinID);
		}
		public DataTable GetReportData(int pintMasterID, int pintPurpose)
		{
			var dsMaster = new IV_MiscellaneousIssueMasterDS();
			return dsMaster.GetReportData(pintMasterID, pintPurpose);
		}
		public string GetRequiredMaster(int pintMasterID)
		{
			var dsMaster = new IV_MiscellaneousIssueMasterDS();
			return dsMaster.GetRequiredMaster(pintMasterID);
		}

        public int Insert(object pobjMaster, DataSet pdstData, out decimal odecQuantity, int purposeID)
        {
            const string methodName = "PCSComMaterials.Inventory.BO.IIVMiscellaneousIssueBO.Insert()";
            odecQuantity = 0;
            //add master
            var voMaster = (IV_MiscellaneousIssueMasterVO)pobjMaster;
            int iTransType = new MST_TranTypeDS().GetTranTypeID(TransactionTypeEnum.IVMiscellaneousIssue.ToString());
            int issueMasterID;
            using (var trans = new TransactionScope())
            {
                using (var db = new PCSDataContext(Utils.Instance.ConnectionString))
                {
                    try
                    {
                        #region Insert Master

                        var issueMaster = new IV_MiscellaneousIssueMaster
                                            {
                                                PostDate = voMaster.PostDate,
                                                Comment = voMaster.Comment,
                                                TransNo = voMaster.TransNo,
                                                UserName = voMaster.UserName
                                            };
                        if (voMaster.LastChange != DateTime.MinValue)
                        {
                            issueMaster.LastChange = voMaster.LastChange;
                        }
                        issueMaster.CCNID = voMaster.CCNID;
                        if (voMaster.DesBinID > 0)
                        {
                            issueMaster.DesBinID = voMaster.DesBinID;
                        }
                        if (voMaster.SourceBinID > 0)
                        {
                            issueMaster.SourceBinID = voMaster.SourceBinID;
                        }
                        if (voMaster.DesLocationID > 0)
                        {
                            issueMaster.DesLocationID = voMaster.DesLocationID;
                        }
                        if (voMaster.SourceLocationID > 0)
                        {
                            issueMaster.SourceLocationID = voMaster.SourceLocationID;
                        }
                        if (voMaster.DesMasLocationID > 0)
                        {
                            issueMaster.DesMasLocationID = voMaster.DesMasLocationID;
                        }
                        if (voMaster.SourceMasLocationID > 0)
                        {
                            issueMaster.SourceMasLocationID = voMaster.SourceMasLocationID;
                        }
                        if (voMaster.PartyID > 0)
                        {
                            issueMaster.PartyID = voMaster.PartyID;
                        }
                        if (voMaster.SaleOrderDetailID > 0)
                        {
                            issueMaster.SaleOrderDetailID = voMaster.SaleOrderDetailID;
                        }
                        if (voMaster.RequiredMaterialMasterID > 0)
                        {
                            issueMaster.RequiredMaterialMasterID = voMaster.RequiredMaterialMasterID;
                        }
                        if (voMaster.InvoiceMasterID > 0)
                        {
                            issueMaster.InvoiceMasterID = voMaster.InvoiceMasterID;
                        }
                        if (voMaster.IssuePurposeID > 0)
                        {
                            issueMaster.IssuePurposeID = voMaster.IssuePurposeID;
                        }
                        
                        #endregion

                        #region insert detail

                        if (pdstData != null && pdstData.Tables.Count > 0)
                        {
                            foreach (DataRow dr in pdstData.Tables[0].Rows)
                            {
                                if (dr.RowState == DataRowState.Deleted)
                                {
                                    continue;
                                }

                                var issueDetail = new IV_MiscellaneousIssueDetail
                                {
                                    Lot = dr[IV_MiscellaneousIssueDetailTable.LOT_FLD].ToString()
                                };

                                if (dr[IV_MiscellaneousIssueDetailTable.QUANTITY_FLD] != DBNull.Value)
                                {
                                    issueDetail.Quantity = Convert.ToDecimal(dr[IV_MiscellaneousIssueDetailTable.QUANTITY_FLD]);
                                }
                                if (dr[IV_MiscellaneousIssueDetailTable.MISCELLANEOUSISSUEMASTERID_FLD] != DBNull.Value)
                                {
                                    issueDetail.MiscellaneousIssueMasterID = Convert.ToInt32(dr[IV_MiscellaneousIssueDetailTable.MISCELLANEOUSISSUEMASTERID_FLD]);
                                }
                                if (dr[IV_MiscellaneousIssueDetailTable.PRODUCTID_FLD] != DBNull.Value)
                                {
                                    issueDetail.ProductID = Convert.ToInt32(dr[IV_MiscellaneousIssueDetailTable.PRODUCTID_FLD]);
                                }
                                if (dr[IV_MiscellaneousIssueDetailTable.AVAILABLEQTY_FLD] != DBNull.Value)
                                {
                                    issueDetail.AvailableQty = Convert.ToDecimal(dr[IV_MiscellaneousIssueDetailTable.AVAILABLEQTY_FLD]);
                                }
                                if (dr[IV_MiscellaneousIssueDetailTable.STOCKUMID_FLD] != DBNull.Value)
                                {
                                    issueDetail.StockUMID = Convert.ToInt32(dr[IV_MiscellaneousIssueDetailTable.STOCKUMID_FLD]);
                                }
                                // insert detail to master issue
                                issueMaster.IV_MiscellaneousIssueDetails.Add(issueDetail);
                            }
                        }

                        // insert issue master and details to database
                        db.IV_MiscellaneousIssueMasters.InsertOnSubmit(issueMaster);
                        db.SubmitChanges();
                        issueMasterID = issueMaster.MiscellaneousIssueMasterID;

                        foreach (IV_MiscellaneousIssueDetail detail in issueMaster.IV_MiscellaneousIssueDetails)
                        {
                            int intProductId = detail.ProductID;
                            var product = detail.ITM_Product;
                            decimal rate = 1;
                            // purpose = xuat vat tu xuong xi nghiep san xuat
                            if (purposeID == 21)
                            {
                                if (product != null && product.Rate != null)
                                {
                                    rate = Convert.ToDecimal(product.Rate);
                                }
                            }
                            decimal decTransferQty = detail.Quantity;
                            // need to add rate if product is not CTTC
                            if (!product.Revision.Equals("CTTC", StringComparison.OrdinalIgnoreCase))
                            {
                                decTransferQty = decTransferQty * rate;
                            }
                            
                            #region inventory

                            #region subtract from source

                            var objBinSource = db.IV_BinCaches.FirstOrDefault(e => e.LocationID == voMaster.SourceLocationID && e.BinID == voMaster.SourceBinID && e.ProductID == intProductId);
                            if (objBinSource != null)
                            {
                                if (objBinSource.OHQuantity - decTransferQty >= 0)
                                {

                                    objBinSource.OHQuantity = objBinSource.OHQuantity - decTransferQty;
                                }
                                else
                                {
                                    _logger.Error(string.Format("Not enough quantity for Bin Cache. Bin ID: {0}. ProductID: {1}", voMaster.SourceBinID, detail.ProductID));
                                    throw new PCSException(ErrorCode.MESSAGE_MATERIALISSUE_INVALID_ISSUE_QTY, product.Code, null);
                                }
                            }

                            var objSourceLocation = db.IV_LocationCaches.FirstOrDefault(e => e.LocationID == voMaster.SourceLocationID && e.ProductID == intProductId);
                            if (objSourceLocation != null)
                            {
                                objSourceLocation.OHQuantity = objSourceLocation.OHQuantity ?? 0;
                                if (objSourceLocation.OHQuantity - decTransferQty >= 0)
                                {
                                    objSourceLocation.OHQuantity = objSourceLocation.OHQuantity - decTransferQty;
                                }
                                else
                                {
                                    // log information and ignore. Will update the quantity for loction based on quantity from bin later
                                    _logger.Debug(string.Format("Not enough quantity for Location Cache. Location ID: {0}. ProductID: {1}", voMaster.SourceLocationID, detail.ProductID));
                                }
                            }
                            var objSourceMasLocation = db.IV_MasLocCaches.FirstOrDefault(e => e.ProductID == intProductId && e.MasterLocationID == voMaster.SourceMasLocationID);
                            if (objSourceMasLocation != null)
                            {
                                objSourceMasLocation.OHQuantity = objSourceMasLocation.OHQuantity ?? 0;
                                if (objSourceMasLocation.OHQuantity - decTransferQty >= 0)
                                {
                                    objSourceMasLocation.OHQuantity = objSourceMasLocation.OHQuantity - decTransferQty;
                                }
                                else
                                {
                                    // log information and ignore. Will update the quantity for master based on quantity from bin later
                                    _logger.Error(string.Format("Not enough quantity for MasLocCache. ProductID: {0}", detail.ProductID));
                                }
                            }

                            #endregion

                            #region add to destination

                            if (voMaster.DesMasLocationID > 0)
                            {
                                var objBin =
                                    db.IV_BinCaches.FirstOrDefault(
                                        e =>
                                        e.LocationID == voMaster.DesLocationID && e.BinID == voMaster.DesBinID &&
                                        e.ProductID == intProductId);
                                if (objBin != null)
                                {
                                    objBin.OHQuantity = objBin.OHQuantity + decTransferQty;
                                }
                                else
                                {
                                    var newBinCache = new IV_BinCache
                                                          {
                                                              CCNID = voMaster.CCNID,
                                                              MasterLocationID = voMaster.DesMasLocationID,
                                                              LocationID = voMaster.DesLocationID
                                                          };
                                    if (voMaster.DesBinID > 0)
                                    {
                                        newBinCache.BinID = voMaster.DesBinID;
                                    }
                                    newBinCache.ProductID = intProductId;
                                    newBinCache.OHQuantity = decTransferQty;
                                    db.IV_BinCaches.InsertOnSubmit(newBinCache);
                                }
                                var objLocation =
                                    db.IV_LocationCaches.FirstOrDefault(
                                        e => e.LocationID == voMaster.DesLocationID && e.ProductID == intProductId);
                                if (objLocation != null)
                                {
                                    objLocation.OHQuantity = objLocation.OHQuantity ?? 0;
                                    objLocation.OHQuantity = objLocation.OHQuantity + decTransferQty;
                                }
                                else
                                {
                                    // add new record
                                    var newLocCache = new IV_LocationCache
                                                          {
                                                              CCNID = voMaster.CCNID,
                                                              MasterLocationID = voMaster.DesMasLocationID,
                                                              LocationID = voMaster.DesLocationID,
                                                              ProductID = intProductId,
                                                              OHQuantity = decTransferQty
                                                          };
                                    db.IV_LocationCaches.InsertOnSubmit(newLocCache);
                                }
                            }
                            var objMasLocation =
                                db.IV_MasLocCaches.FirstOrDefault(
                                    e => e.ProductID == intProductId && e.MasterLocationID == voMaster.DesMasLocationID);
                            if (objMasLocation != null)
                            {
                                objMasLocation.OHQuantity = objMasLocation.OHQuantity ?? 0;
                                objMasLocation.OHQuantity = objMasLocation.OHQuantity + decTransferQty;
                            }
                            else
                            {
                                // add new record
                                var newMasLocCache = new IV_MasLocCache
                                                         {
                                                             CCNID = voMaster.CCNID,
                                                             MasterLocationID = voMaster.DesMasLocationID,
                                                             ProductID = intProductId,
                                                             OHQuantity = decTransferQty
                                                         };
                                db.IV_MasLocCaches.InsertOnSubmit(newMasLocCache);
                            }

                            #endregion

                            // Update actual received
                            if (voMaster.RequiredMaterialMasterID > 0)
                            {
                                decimal decActualReceived = detail.Quantity;
                                var requiredDetail = db.PRO_RequiredMaterialDetails.Where(
                                        e => e.ProductID == intProductId && e.RequiredMaterialMasterID == voMaster.RequiredMaterialMasterID).ToList();
                                // update actual received quantity for all line item
                                requiredDetail.ForEach(d => d.ActualReceived = d.ActualReceived.GetValueOrDefault(0) + decActualReceived);
                            }

                            #endregion

                            #region Transaction history

                            var sourceTransaction = new MST_TransactionHistory
                                               {
                                                   CCNID = voMaster.CCNID,
                                                   StockUMID = detail.StockUMID,
                                                   MasterLocationID = voMaster.SourceMasLocationID,
                                                   ProductID = detail.ProductID,
                                                   LocationID = voMaster.SourceLocationID,
                                                   BinID = voMaster.SourceBinID
                                               };
                            if (voMaster.PartyID > 0)
                            {
                                sourceTransaction.PartyID = voMaster.PartyID;
                            }
                            sourceTransaction.RefMasterID = issueMaster.MiscellaneousIssueMasterID;
                            sourceTransaction.RefDetailID = detail.MiscellaneousIssueDetailID;
                            sourceTransaction.PostDate = issueMaster.PostDate;
                            sourceTransaction.TransDate = DateTime.Now;
                            sourceTransaction.Quantity = -decTransferQty;
                            sourceTransaction.TranTypeID = iTransType;
                            db.MST_TransactionHistories.InsertOnSubmit(sourceTransaction);
                            
                            var destTransaction = new MST_TransactionHistory
                                                  {
                                                      CCNID = voMaster.CCNID,
                                                      StockUMID = detail.StockUMID,
                                                      MasterLocationID = voMaster.SourceMasLocationID,
                                                      ProductID = detail.ProductID,
                                                      LocationID = voMaster.SourceLocationID,
                                                      BinID = voMaster.SourceBinID
                                                  };

                            if (voMaster.PartyID > 0)
                            {
                                destTransaction.PartyID = voMaster.PartyID;
                            }
                            destTransaction.RefMasterID = issueMaster.MiscellaneousIssueMasterID;
                            destTransaction.RefDetailID = detail.MiscellaneousIssueDetailID;
                            destTransaction.PostDate = issueMaster.PostDate;
                            destTransaction.TransDate = DateTime.Now;
                            destTransaction.Quantity = -decTransferQty;
                            destTransaction.TranTypeID = iTransType;

                            if (voMaster.DesMasLocationID > 0)
                            {
                                destTransaction.CCNID = voMaster.CCNID;
                                destTransaction.MasterLocationID = voMaster.DesMasLocationID;
                                destTransaction.LocationID = voMaster.DesLocationID;
                                destTransaction.BinID = voMaster.DesBinID;
                                if (destTransaction.BinID <= 0)
                                {
                                    destTransaction.BinOHQuantity = 0;
                                }
                                destTransaction.Quantity = decTransferQty;

                            }
                            db.MST_TransactionHistories.InsertOnSubmit(destTransaction);
                            db.SubmitChanges();

                            #endregion
                        }

                        #endregion

                        // update quantity for source location and master location
                        // by compute the sum quantity from bin
                        var productList = issueMaster.IV_MiscellaneousIssueDetails.Select(d => d.ProductID);
                        foreach (int productId in productList)
                        {
                            #region Location cache

                            var query = db.IV_BinCaches.Where(b => b.ProductID == productId && b.LocationID == voMaster.SourceLocationID);
                            decimal onhandQuantity;
                            decimal commitQuantity;
                            if (query != null && query.Any())
                            {
                                onhandQuantity = query.Sum(b => b.OHQuantity.GetValueOrDefault(0));
                                commitQuantity = query.Sum(b => b.CommitQuantity.GetValueOrDefault(0));
                                var location = db.IV_LocationCaches.FirstOrDefault(l => l.ProductID == productId && l.LocationID == voMaster.SourceLocationID);
                                if (location != null)
                                {
                                    location.OHQuantity = onhandQuantity;
                                    location.CommitQuantity = commitQuantity;
                                }
                                else
                                {
                                    location = new IV_LocationCache
                                                   {
                                                       CCNID = voMaster.CCNID,
                                                       LocationID = voMaster.SourceLocationID,
                                                       MasterLocationID = voMaster.SourceMasLocationID,
                                                       ProductID = productId,
                                                       OHQuantity = onhandQuantity,
                                                       CommitQuantity = commitQuantity
                                                   };
                                    db.IV_LocationCaches.InsertOnSubmit(location);
                                }
                            }

                            #endregion

                            #region master location cache

                            query = db.IV_BinCaches.Where(b => b.ProductID == productId && b.MasterLocationID == voMaster.SourceMasLocationID);
                            if (query != null && query.Any())
                            {
                                onhandQuantity = query.Sum(b => b.OHQuantity.GetValueOrDefault(0));
                                commitQuantity = query.Sum(b => b.CommitQuantity.GetValueOrDefault(0));
                                var masLocCache = db.IV_MasLocCaches.FirstOrDefault(l => l.ProductID == productId && l.MasterLocationID == voMaster.SourceMasLocationID);
                                if (masLocCache != null)
                                {
                                    masLocCache.OHQuantity = onhandQuantity;
                                    masLocCache.CommitQuantity = commitQuantity;
                                }
                                else
                                {
                                    masLocCache = new IV_MasLocCache
                                    {
                                        CCNID = voMaster.CCNID,
                                        MasterLocationID = voMaster.SourceMasLocationID,
                                        ProductID = productId,
                                        OHQuantity = onhandQuantity,
                                        CommitQuantity = commitQuantity
                                    };
                                    db.IV_MasLocCaches.InsertOnSubmit(masLocCache);
                                }
                            }

                            #endregion
                        }

                        // submit changes to database
                        db.SubmitChanges();
                    }
                    catch (PCSBOException ex)
                    {
                        if (ex.mCode == ErrorCode.SQLDUPLICATE_KEYCODE)
                            throw new PCSDBException(ErrorCode.DUPLICATE_KEY, methodName, ex);
                        if (ex.mCode == ErrorCode.MESSAGE_MATERIALISSUE_INVALID_ISSUE_QTY)
                            throw new PCSDBException(ErrorCode.MESSAGE_NOT_ENOUGH_COMPONENT_TO_COMPLETE, ex.mMethod, ex);
                        throw new PCSDBException(ErrorCode.ERROR_DB, methodName, ex);
                    }
                }
                trans.Complete();
            }
            return issueMasterID;
        }
	}
}

